package cn.net.communion.dbdatasync.dbhelper.impl;

import cn.net.communion.dbdatasync.Tool;
import cn.net.communion.dbdatasync.dbhelper.DbHelper;
import cn.net.communion.dbdatasync.entity.JobInfo;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MySql implements DbHelper {
    private Logger logger = Logger.getLogger(MySql.class);

    public String assembleSQL(String srcSql, Connection inConnection, Connection outConnection, JobInfo jobInfo) throws SQLException {
        String uniqueName = Tool.generateString(6) + "_" + jobInfo.getName();
        String[] fields = jobInfo.getDestTableFields().split(",");
        String[] updateFields = jobInfo.getDestTableUpdate().split(",");
        String destTable = jobInfo.getDestTable();
        String destTableKey = jobInfo.getDestTableKey();
        PreparedStatement pst = inConnection.prepareStatement(srcSql);
        ResultSet rs = pst.executeQuery();
//        StringBuffer sql = new StringBuffer();
        /*sql.append("insert into ").append(jobInfo.getDestTable()).append(" (").append(jobInfo.getDestTableFields()).append(") values ");*/
//        sql.append("insert all ");
        long count = 0;
        while (rs.next()) {
            String querySql = "select count(*) from " + jobInfo.getDestTable() + " where " + jobInfo.getPramrykey() + " = '" + rs.getString(jobInfo.getPramrykey()) + "'";
            PreparedStatement queryStatement = outConnection.prepareStatement(querySql);
            ResultSet resultSet = queryStatement.executeQuery();
            resultSet.next();
            int anInt = resultSet.getInt(1);
            if (anInt == 0) { //SELECT COUNT(*)FROM A WHERE RKEY= 'RKEY'
                StringBuffer sql = new StringBuffer();
                jobInfo.getPramrykey();
                sql.append("insert into " + jobInfo.getDestTable() + " (" + jobInfo.getDestTableFields() + ")VALUES(");
                for (int index = 0; index < fields.length; index++) {
//                sql.append("'").append(rs.getString(fields[index])).append(index == (fields.length - 1) ? "'" : "',");
                    sql.append(index == (fields.length - 1) ? "?)" : "?,");
                }
                PreparedStatement statement = outConnection.prepareStatement(sql.toString());
                for (int index = 0; index < fields.length; index++) {
                    statement.setString(index + 1, rs.getString(fields[index]));
                }
                statement.executeUpdate();
                outConnection.commit();
                statement.close();
                logger.info("插入第" + ++count + "条数据");
            } else {
                logger.info("正在跳过重复数据。。");
            }
            queryStatement.close();
        }

        if (rs != null) {
            rs.close();
        }
        if (pst != null) {
            pst.close();
        }
//        if (count > 0) {
//            /* sql = sql.deleteCharAt(sql.length() - 1);*/
//            sql.append("SELECT 1 FROM DUAL");
////            sql.append(";");
////            if ((!jobInfo.getDestTableUpdate().equals("")) && (!jobInfo.getDestTableKey().equals(""))) {
////                sql.append(" on duplicate key update ");
////                for (int index = 0; index < updateFields.length; index++) {
////                    sql.append(updateFields[index]).append("= values(").append(updateFields[index]).append(index == (updateFields.length - 1) ? ")" : "),");
////                }
////                return new StringBuffer("alter table ").append(destTable).append(" add constraint ").append(uniqueName).append(" unique (").append(destTableKey).append(");").append(sql.toString())
////                                .append(";alter table ").append(destTable).append(" drop index ").append(uniqueName).toString();
////            }
////            return sql.toString();
//        }
//        //System.out.println(sql.toString());
        return null;
    }

    public void executeSQL(String sql, Connection conn) throws SQLException {
        PreparedStatement pst = conn.prepareStatement(sql);
//        String[] sqlList = sql.split(";");
//        for (int index = 0; index < sqlList.length; index++) {
//            pst.addBatch(sqlList[index]);
//        }
//        pst.executeBatch();
        // insert all into T_ITEMSTOCK_FZ (ITEMNUMBER,ITEMNAME,ITEMMODEL,ITEMLOT,QTY,STOCKNAME,STOCKID,INSTOCKDATE)VALUES('M1.01.00365','覆铜板','LF7022R','00004.0002.16111502.01-1','12.1000000000','待检仓','20727','2016-11-15 17:06:49.613')SELECT 1 FROM DUAL;
        pst.executeUpdate();
        conn.commit();
        pst.close();
    }
}
